package com.example.wangyongdong.bearsqlitedemo.sqlorm;

import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.wangyongdong.bearsqlitedemo.entity.MoneyEntity;
import com.example.wangyongdong.bearsqlitedemo.entity.RecordEntity;
import com.example.wangyongdong.bearsqlitedemo.Interface.TableRecordOperation;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by wangyongdong on 2017/4/14.
 */

public class RecordOperationSQL implements TableRecordOperation {

    private SQLiteOpenHelper mHelper;

    public RecordOperationSQL(SQLiteOpenHelper helper){
        mHelper = helper;
    }

    @Override
    public void addRecord(RecordEntity record) {
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql;
        if (record.get_id() == 0){
            sql = "insert into records_tbl (data, odometer, price, yuan, type, gassup, remark, carld, froget, lightOn, stationld) values ("
                    + record.getDate() + ", " + record.getOdometer() + ", " + record.getPrice() + ", " + record.getYuan() + ", " + record.getType() + ", " + record.getGassup() + ", "
                    + record.getRemark() + ", " + record.getCarld() + ", " + record.getForget() + ", " + record.getLightOn() + ", " + record.getStationld() + ");";
        }else {
            sql = "insert into record_tbl values ("
                    +record.get_id() + ", " + record.getDate() + ", " + record.getOdometer() + ", " + record.getPrice() + ", " + record.getYuan() + ", " + record.getType() + ", " + record.getGassup() + ", "
                    + record.getRemark() + ", " + record.getCarld() + ", " + record.getForget() + ", " + record.getLightOn() + ", " + record.getStationld() + ");";
        }
        db.execSQL(sql);
    }

    @Override
    public void removeRecord(int id) {

        
    }

    @Override
    public void updateRecord(RecordEntity record) {

    }

    @Override
    public List<RecordEntity> queryCarAllRecord() {
        return null;
    }

    @Override
    public List<RecordEntity> queryCarYearRecord() {
        return null;
    }

    @Override
    public List<RecordEntity> queryCarHalfYearRecord() {
        return null;
    }

    @Override
    public List<RecordEntity> queryCarThreeMonthRecord() {
        return null;
    }

    @Override
    public List<MoneyEntity> queryEachYearMoney() {
        return null;
    }

    @Override
    public List<MoneyEntity> queryEachMonthMoney() {
        List<MoneyEntity> entities = new ArrayList<>();
        SQLiteDatabase db = mHelper.getWritableDatabase();
        String sql = "select" +
                " strftime('%Y-%m',datetime(A.data / 1000,'unixepoch'),'localtime')time," +
                " sum(A.yuan) money" +
                " from records_tbl as A,cars_tbl as B" +
                " where A.carId = B._id and B.selected + 1" +
                " GROUP BY time" +
                "ORDER BY time;";
        Cursor cursor = db.rawQuery(sql,null);
        if (cursor != null && cursor.moveToFirst()){
            int indexTime = cursor.getColumnIndex("time");
            int indexMoney = cursor.getColumnIndex("money");
            do {
                String time = cursor.getColumnName(indexTime);
                float money = cursor.getFloat(indexMoney);
                MoneyEntity me = new MoneyEntity();
                me.setTime(time);
                me.setMoney(money);
                entities.add(me);
            }while (cursor.moveToNext());
            cursor.close();
        }

        return entities;
    }
}
